﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;

namespace Camalot.Common.Data {
	public abstract class SqlDataProvider : IDataProvider {

		/// <summary>
		/// Initializes a new instance of the <see cref="BaseSqlDataProvider"/> class.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		/// <remarks>Be sure to initialize your Connection object in the SubClass constructor</remarks>
		public SqlDataProvider( String connectionString ) {
			if ( ConfigurationManager.ConnectionStrings[connectionString] == null ) {
				throw new ArgumentException ( String.Format ( "Unable to locate referenced connection string: {0}", connectionString ) );
			}

			ConnectionString = ConfigurationManager.ConnectionStrings[connectionString];
		}

		/// <summary>
		/// Initializes a new instance of the <see cref="BaseSqlDataProvider"/> class.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		/// <remarks>Be sure to initialize your Connection object in the SubClass constructor</remarks>
		public SqlDataProvider( ConnectionStringSettings connectionString ) {
			if ( connectionString == null || String.IsNullOrEmpty ( connectionString.ConnectionString ) ) {
				throw new ArgumentNullException ( "connectionString cannot be null" );
			}

			ConnectionString = connectionString;
		}


		/// <summary>
		/// Initializes a new instance of the <see cref="SqlDataProvider"/> class.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		/// <param name="connection">The connection.</param>
		public SqlDataProvider( String connectionString, IDbConnection connection ) {
			if ( ConfigurationManager.ConnectionStrings[connectionString] == null ) {
				throw new ArgumentException ( String.Format ( "Unable to locate referenced connection string: {0}", connectionString ) );
			}

			ConnectionString = ConfigurationManager.ConnectionStrings[connectionString];
			Connection = connection;
		}

		/// <summary>
		/// Initializes a new instance of the <see cref="BaseSqlDataProvider"/> class.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		/// <param name="connection">The connection.</param>
		public SqlDataProvider( ConnectionStringSettings connectionString, IDbConnection connection ) {
			if ( connectionString == null || String.IsNullOrEmpty ( connectionString.ConnectionString ) ) {
				throw new ArgumentNullException ( "connectionString cannot be null" );
			}

			ConnectionString = connectionString;
			Connection = connection;
		}


		/// <summary>
		/// Executes the specified sql returning a data reader.
		/// </summary>
		/// <param name="sql">The SQL command.</param>
		/// <returns></returns>
		public System.Data.IDataReader ExecuteReader( string sql ) {
			return ExecuteReader ( sql, CommandType.Text, null );
		}

		/// <summary>
		/// Executes the specified sql returning a data reader.
		/// </summary>
		/// <param name="sql">The SQL command.</param>
		/// <param name="params">The command params.</param>
		/// <returns></returns>
		public System.Data.IDataReader ExecuteReader( string sql, params System.Data.IDataParameter[] @params ) {
			return ExecuteReader ( sql, CommandType.StoredProcedure, @params );
		}

		/// <summary>
		/// Executes the specified sql returning a data reader.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="type">The command type.</param>
		/// <param name="params">The command params.</param>
		/// <returns></returns>
		public System.Data.IDataReader ExecuteReader( string sql, CommandType type, params System.Data.IDataParameter[] @params ) {
			Open ( );
			IDbCommand cmd = Connection.CreateCommand ( );
			cmd.CommandText = sql;
			cmd.CommandType = type;
			if ( @params != null ) {
				foreach ( var item in @params ) {
					cmd.Parameters.Add ( item );
				}
			}
			return cmd.ExecuteReader ( );
		}

		/// <summary>
		/// Executes the specified sql returning a scalar value.
		/// </summary>
		/// <typeparam name="T">The result</typeparam>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		public T ExecuteScalar<T>( string sql ) {
			return ExecuteScalar<T> ( sql, CommandType.Text, null );
		}

		/// <summary>
		/// Executes the specified sql returning a scalar value.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">The SQL command.</param>
		/// <param name="params">The command params.</param>
		/// <returns></returns>
		public T ExecuteScalar<T>( string sql, params System.Data.IDataParameter[] @params ) {
			return ExecuteScalar<T> ( sql, CommandType.StoredProcedure, @params );
		}

		/// <summary>
		/// Executes the specified sql returning a scalar value.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">The SQL command.</param>
		/// <param name="type">The command type.</param>
		/// <param name="params">The command params.</param>
		/// <returns></returns>
		public T ExecuteScalar<T>( string sql, CommandType type, params System.Data.IDataParameter[] @params ) {
			Open ( );
			IDbCommand cmd = Connection.CreateCommand ( );
			cmd.CommandText = sql;
			cmd.CommandType = type;
			if ( @params != null ) {
				foreach ( var item in @params ) {
					cmd.Parameters.Add ( item );
				}
			}
			return (T)cmd.ExecuteScalar ( );

		}

		/// <summary>
		/// Executes the specified sql returning the number of rows affected.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		public int ExecuteNonQuery( String sql ) {
			return ExecuteNonQuery ( sql, CommandType.Text, null );
		}

		/// <summary>
		/// Executes the specified sql returning the number of rows affected.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="params">The @params.</param>
		/// <returns></returns>
		public int ExecuteNonQuery( string sql, params System.Data.IDataParameter[] @params ) {
			return ExecuteNonQuery ( sql, CommandType.StoredProcedure, @params );
		}

		/// <summary>
		/// Executes the specified sql returning the number of rows affected.
		/// </summary>
		/// <param name="sql">The SQL command.</param>
		/// <param name="type">The command type.</param>
		/// <param name="params">The command params.</param>
		/// <returns></returns>
		public int ExecuteNonQuery( string sql, CommandType type, params System.Data.IDataParameter[] @params ) {
			Open ( );
			IDbCommand cmd = Connection.CreateCommand ( );
			cmd.CommandText = sql;
			cmd.CommandType = type;
			if ( @params != null ) {
				foreach ( var item in @params ) {
					cmd.Parameters.Add ( item );
				}
			}
			return cmd.ExecuteNonQuery ( );
		}

		/// <summary>
		/// Opens the connection to the data source.
		/// </summary>
		public void Open( ) {
			if ( Connection == null ) {
				throw new ArgumentException ( "Connection object is not initialized. Make sure you initialize the connection object before attempting to open a connection." );
			}

			if ( Connection != null && Connection.State == System.Data.ConnectionState.Closed || Connection.State == System.Data.ConnectionState.Broken ) {
				Connection.Open ( );
			}
		}

		/// <summary>
		/// Closes the connection to the data source.
		/// </summary>
		public void Close( ) {
			if ( Connection != null && Connection.State != System.Data.ConnectionState.Closed ) {
				Connection.Close ( );
			}
		}

		/// <summary>
		/// Gets the connection string.
		/// </summary>
		public ConnectionStringSettings ConnectionString { get; private set; }

		/// <summary>
		/// Gets the connection object. This must be set in the constructor of your subclass.
		/// </summary>
		public System.Data.IDbConnection Connection { get; protected set; }

		/// <summary>
		/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
		/// </summary>
		public virtual void Dispose( ) {
			Close ( );

			if ( Connection != null ) {
				Connection.Dispose ( );
			}
		}
	}
}
